const executeSql = require('../utils/query.js')

exports.selectCategoryPage = async ({page = 1,
                                 pageSize = 5,
                                 category_name,
                                 is_show,
                                 start_date,
                                 end_date,}) => {

    let offset = (page - 1) * pageSize

    let sql = `select t1.* ,t2.nickname from category  t1 left join manager t2 
            on t1.admin_id = t2.admin_id where 1 `

    //  根据查询条件，有传条件，动态的拼接sql语句
    if (category_name) {
        sql += ` and t1.category_name like '%${category_name}%'`
    }
    if (is_show) {
        sql += ` and t1.is_show = ${is_show}`
    }

    if (start_date && !end_date) {
        sql += ` and t1.add_date > ${start_date}`
    }

    if (!start_date && end_date) {
        sql += ` and t1.add_date < ${end_date}`
    }

    if (start_date && end_date) {
        sql += ` and t1.add_date between ${start_date} and ${end_date}`
    }
    sql += ` order by t1.sort limit ${offset},${pageSize}`

    return await executeSql(sql)
}

exports.selectCategoryById = async (id) => {
    let sql = `select * from category where category_id = ${id}`
    return await executeSql(sql)
}

exports.insertCategory = async (data) => {
    let {category_name, sort, is_show, intro, add_date, admin_id} = data
    let sql = `insert into category(category_name, sort, is_show, intro, add_date, admin_id) 
        values('${category_name}', ${sort}, ${is_show}, '${intro}', ${add_date}, ${admin_id})`
    return await executeSql(sql)
}

exports.selectALlCategoryCount = async ({
                                         category_name,
                                         is_show,
                                         start_date,
                                         end_date,
                                     }) => {
    let sql = `select count(category_id) as count from category where 1 `
    //  根据查询条件，有传条件，动态的拼接sql语句
    if (category_name) {
        sql += ` and category_name like '%${category_name}%'`
    }
    if (is_show) {
        sql += ` and is_show = ${is_show}`
    }

    if (start_date && !end_date) {
        sql += ` and add_date > ${start_date}`
    }

    if (!start_date && end_date) {
        sql += ` and add_date < ${end_date}`
    }

    if (start_date && end_date) {
        sql += ` and add_date between ${start_date} and ${end_date}`
    }
    let result = await executeSql(sql)
    return result[0].count
}

exports.delCategoryById = async (category_id = 0) => {
    let sql = `delete from category where category_id = ${category_id}`
    return await executeSql(sql)
}

exports.delBatchCategory = async (category_ids = 0) => {
    let sql = `delete from category where category_id in (${category_ids})`
    return await executeSql(sql)
}

// 校验添加时分类名是否重名
exports.checkAddCategoryName = async (category_name) => {
    let sql = `select * from category where category_name = '${category_name}'`

    let result = await executeSql(sql)

    if (result.length > 0) {
        // 有重复
        return false
    }
    return true
}

// 校验修改时分类名是否重名
exports.checkUpdateCategoryName = async (category_id, category_name) => {
    let sql = `select category_id from category where category_name = '${category_name}' and category_id != ${category_id}`

    let result = await executeSql(sql)

    if (result.length > 0) {
        // 有重复
        return false
    }
    return true
}

exports.updateCategoryById = async (data) => {
    let {category_id, category_name, sort, is_show, intro, add_date, admin_id} = data
    let sql = `update category set category_name = '${category_name}',sort = ${sort},is_show = ${is_show},intro = '${intro}',
        add_date = ${add_date},admin_id = ${admin_id} where category_id = ${category_id};`
    return await executeSql(sql)
}

exports.selectAllCategory = async () => {
    let sql = `select * from category`
    return await executeSql(sql)
}